CREATE DATABASE IF NOT EXISTS Inversion;
USE Inversion;

CREATE TABLE usuario (
    idusu INT AUTO_INCREMENT PRIMARY KEY,
    nomusu VARCHAR(100) NOT NULL,
    usu VARCHAR(50) NOT NULL UNIQUE,
    conusu VARCHAR(255) NOT NULL
);

CREATE TABLE inversionista (
    idinv INT AUTO_INCREMENT PRIMARY KEY,
    nominv VARCHAR(150) NOT NULL,
    mailinv VARCHAR(150) UNIQUE,
    dirinv TEXT,
    telinv VARCHAR(20)
);

CREATE TABLE inversion (
    idreginv INT AUTO_INCREMENT PRIMARY KEY,
    idinv INT NOT NULL,
    fecinv DATE NOT NULL,
    mntinv DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (idinv) REFERENCES inversionista(idinv) ON DELETE CASCADE
);

CREATE TABLE retiro (
    idregret INT AUTO_INCREMENT PRIMARY KEY,
    idinv INT NOT NULL,
    fecret DATE NOT NULL,
    mntret DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (idinv) REFERENCES inversionista(idinv) ON DELETE CASCADE
);

CREATE TABLE saldos (
    idinv INT NOT NULL,
    saldo DECIMAL(10,2) NOT NULL,
    fecha DATE NOT NULL,
    tipo ENUM('Acumulado', 'Corte Mensual') NOT NULL,
    PRIMARY KEY (idinv, fecha),
    FOREIGN KEY (idinv) REFERENCES inversionista(idinv) ON DELETE CASCADE
);
